package br.com.sgbl.dao;

import br.com.sgbl.bean.Setor;
import br.com.sgbl.utils.Conexao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author heidi
 */
public class SetorDao {

    private static final String SQL_LISTAR =
            "SELECT * FROM setor";
    private static final String SQL_INSERIR =
            "INSERT INTO setor (id, descricao) VALUES (?,?)";
    private static final String SQL_ALTERAR =
            "UPDATE setor SET descricao = ? WHERE id = ?";
    private static final String SQL_EXCLUIR =
            "DELETE FROM setor WHERE id = ?";

    public Setor localizar(Connection conn, long codigo) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(SQL_LISTAR + " where id= ? order by id");
        ps.setLong(1, codigo);

        ResultSet rs = ps.executeQuery();

        if (rs.first()) {
            Setor setor = new Setor();
            setor.setId(rs.getLong("id"));
            setor.setDescricao(rs.getString("descricao"));
            
            return setor;
        } else {
            return null;
        }
    }

    public Setor salvar(Setor setor) throws SQLException {
        if (setor.getId() == 0) {
            return inserir(setor);
        } else {
            return alterar(setor);
        }
    }

    private Setor inserir(Setor setor) throws SQLException {
        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_INSERIR,
                    PreparedStatement.RETURN_GENERATED_KEYS);

            ps.setNull(1, Types.NULL);
            ps.setString(2, setor.getDescricao());

            ps.executeUpdate();

            ResultSet rs = ps.getGeneratedKeys();
            rs.first();

            setor.setId(rs.getLong("GENERATED_KEY"));

            conn.commit();
        } finally {
            conn.close();
        }

        return setor;
    }

    private Setor alterar(Setor setor) throws SQLException {
        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_ALTERAR);

            ps.setString(1, setor.getDescricao());
            ps.setLong(2, setor.getId());


            ps.executeUpdate();

            conn.commit();
        } finally {
            conn.close();
        }

        return setor;
    }

    public List listar() throws SQLException {
        List lista = new ArrayList();

        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_LISTAR);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Setor setor = new Setor();

                setor.setId(rs.getLong("id"));
                setor.setDescricao(rs.getString("descricao"));



                lista.add(setor);
            }
        } finally {
            conn.close();
        }

        return lista;
    }

    public void excluir(Setor setor) throws SQLException {
        Connection conn = Conexao.getConnection();

        try {
            PreparedStatement ps = conn.prepareStatement(SQL_EXCLUIR);

            ps.setLong(1, setor.getId());

            ps.executeUpdate();

            conn.commit();
        } finally {
            conn.close();
        }
    }
}
